package com.mobile.util;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import net.sourceforge.pinyin4j.PinyinHelper;
import net.sourceforge.pinyin4j.format.HanyuPinyinCaseType;
import net.sourceforge.pinyin4j.format.HanyuPinyinOutputFormat;
import net.sourceforge.pinyin4j.format.HanyuPinyinToneType;
import org.apache.commons.lang3.StringUtils;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.NumberToTextConverter;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xddf.usermodel.XDDFSolidFillProperties;
import org.apache.poi.xddf.usermodel.chart.*;
import org.apache.poi.xssf.usermodel.*;
import org.openxmlformats.schemas.drawingml.x2006.chart.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * Excel导入导出工具类
 *
 * @author wgq
 * @version 1.0 2021-06-15
 */
public class ExcelUtils {
    protected static Logger logger = LogManager.getLogger(ExcelUtils.class);

    /**
     * excel 导出
     * 根据Map创建对应的Excel(一个excel 创建多个sheet)
     * list –> 多个Map（1.key为title 对应表格Title 2.key为entity 对应表格对应实体 3.key为data Collection 数据）
     *
     * @param list     数据列表
     * @param fileName 导出时的excel名称
     * @param response
     */
    public static void exportExcelMoreSheet(List<Map<String, Object>> list, String fileName, HttpServletResponse response) throws IOException {
        defaultExportMoreSheet(list, fileName, response);
    }

    /**
     * 默认的 excel 导出
     * 根据Map创建对应的Excel(一个excel 创建多个sheet)
     *
     * @param list     数据列表
     * @param fileName 导出时的excel名称
     * @param response
     */
    private static void defaultExportMoreSheet(List<Map<String, Object>> list, String fileName, HttpServletResponse response) throws IOException {
        //把数据添加到excel表格中
        Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.XSSF);
        downLoadExcel(fileName, response, workbook);
    }

    /**
     * excel 导出
     *
     * @param list         数据列表
     * @param pojoClass    pojo类型
     * @param fileName     导出时的excel名称
     * @param response
     * @param exportParams 导出参数（标题、sheet名称、是否创建表头，表格类型）
     */
    private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response, ExportParams exportParams) throws IOException {
        //设置Excel每列标题和边框样式
        exportParams.setStyle(ExcelStyleUtil.class);
        //设置导出为微软07版本Excel
        exportParams.setType(ExcelType.XSSF);
        //把数据添加到excel表格中
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
        downLoadExcel(fileName, response, workbook);
    }

    /**
     * excel 导出
     *
     * @param list      数据列表
     * @param sheetName sheet名称
     * @param pojoClass pojo类型
     * @param fileName  导出时的excel名称
     * @param response
     */
    public static void exportExcel(List<?> list, String sheetName, Class<?> pojoClass, String fileName, HttpServletResponse response) throws IOException {
        defaultExport(list, pojoClass, fileName, response, new ExportParams(null, sheetName, ExcelType.XSSF));
    }

    /**
     * excel 导出
     *
     * @param list         数据列表
     * @param pojoClass    pojo类型
     * @param fileName     导出时的excel名称
     * @param exportParams 导出参数（标题、sheet名称、是否创建表头，表格类型）
     * @param response
     */
    public static void exportExcel(List<?> list, Class<?> pojoClass, String fileName, ExportParams exportParams, HttpServletResponse response) throws IOException {
        defaultExport(list, pojoClass, fileName, response, exportParams);
    }

    /**
     * excel 导出
     *
     * @param list      数据列表
     * @param title     表格内数据标题
     * @param sheetName sheet名称
     * @param pojoClass pojo类型
     * @param fileName  导出时的excel名称
     * @param response
     */
    public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, HttpServletResponse response) throws IOException {
        defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName, ExcelType.XSSF));
    }


    /**
     * excel 导出
     *
     * @param list           数据列表
     * @param title          表格内数据标题
     * @param sheetName      sheet名称
     * @param pojoClass      pojo类型
     * @param fileName       导出时的excel名称
     * @param isCreateHeader 是否创建表头
     * @param response
     */
    public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, boolean isCreateHeader, HttpServletResponse response) throws IOException {
        ExportParams exportParams = new ExportParams(title, sheetName, ExcelType.XSSF);
        exportParams.setCreateHeadRows(isCreateHeader);
        defaultExport(list, pojoClass, fileName, response, exportParams);
    }


    /**
     * excel下载
     *
     * @param fileName 下载时的文件名称
     * @param response
     * @param workbook excel数据
     */
    public static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) throws IOException {
        try {
            response.setCharacterEncoding("UTF-8");
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName + ".xlsx", "UTF-8"));
            workbook.write(response.getOutputStream());
        } catch (Exception e) {
            throw new IOException(e.getMessage());
        }
    }


    /**
     * excel 导入
     *
     * @param file      excel文件
     * @param pojoClass pojo类型
     * @param <T>
     * @return
     */
    public static <T> List<T> importExcel(MultipartFile file, Class<T> pojoClass) throws IOException {
        return importExcel(file, 1, 1, pojoClass);
    }

    /**
     * excel 导入
     *
     * @param filePath   excel文件路径
     * @param titleRows  表格内数据标题行
     * @param headerRows 表头行
     * @param pojoClass  pojo类型
     * @param <T>
     * @return
     */
    public static <T> List<T> importExcel(String filePath, Integer titleRows, Integer headerRows, Class<T> pojoClass) throws IOException {
        if (StringUtils.isBlank(filePath)) {
            return null;
        }
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        params.setNeedSave(true);
        params.setSaveUrl("/excel/");
        try {
            return ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);
        } catch (NoSuchElementException e) {
            throw new IOException("模板不能为空");
        } catch (Exception e) {
            throw new IOException(e.getMessage());
        }
    }


    /**
     * excel 导入
     *
     * @param file       上传的文件
     * @param titleRows  表格内数据标题行
     * @param headerRows 表头行
     * @param pojoClass  pojo类型
     * @param <T>
     * @return
     */
    public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass) throws IOException {
        if (file == null) {
            return null;
        }
        try {
            return importExcel(file.getInputStream(), titleRows, headerRows, pojoClass);
        } catch (Exception e) {
            throw new IOException(e.getMessage());
        }
    }

    /**
     * excel 导入
     *
     * @param inputStream 文件输入流
     * @param titleRows   表格内数据标题行
     * @param headerRows  表头行
     * @param pojoClass   pojo类型
     * @param <T>
     * @return
     */
    public static <T> List<T> importExcel(InputStream inputStream, Integer titleRows, Integer headerRows, Class<T> pojoClass) throws IOException {
        if (inputStream == null) {
            return null;
        }
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        params.setSaveUrl("/excel/");
        params.setNeedSave(true);
        try {
            return ExcelImportUtil.importExcel(inputStream, pojoClass, params);
        } catch (NoSuchElementException e) {
            throw new IOException("excel文件不能为空");
        } catch (Exception e) {
            throw new IOException(e.getMessage());
        }
    }
    /**
     * 读取excel表(设备数据)
     *
     * @param inputStream
     * @return
     */
    public static List<PageData> excelToDeviceDataList(InputStream inputStream) {
        List<PageData> list = new ArrayList<>();
        Workbook workbook = null;
        try {
            workbook = WorkbookFactory.create(inputStream);
            inputStream.close();
            //工作表对象
            Sheet sheet = workbook.getSheetAt(0);
            //工作表的行
            Row row;
            //得到指定的单元格
            Cell cell;
            PageData pd;
            for (int rIndex = sheet.getFirstRowNum() + 1; rIndex <= sheet.getLastRowNum(); rIndex++) {   //遍历行
                System.out.println("rIndex: " + rIndex);
                row = sheet.getRow(rIndex);
                if (row != null) {
                    int firstCellIndex = row.getFirstCellNum();
                    int lastCellIndex = row.getLastCellNum();
                    pd = new PageData();
                    for (int cIndex = firstCellIndex; cIndex < lastCellIndex; cIndex++) {   //遍历列
                        cell = row.getCell(cIndex);
                        String value = "";
                        if (cell.getCellType().equals(CellType.NUMERIC)) {
                            value = NumberToTextConverter.toText(cell.getNumericCellValue()).trim();
                        } else if (cell.getCellType().equals(CellType.STRING)) {
                            value = cell.getStringCellValue().trim();
                        }
                        switch (cIndex) {
                            case 0:
                                pd.put("equipImei", value);
                                break;
                            case 1:
                                pd.put("orderNum", value);
                                break;
                            case 2:
                                pd.put("total", value);
                                break;
                            case 3:
                                pd.put("ngQuntity", value);
                                break;
                            case 4:
                                pd.put("okPlateQuntity", value);
                                break;
                            case 5:
                                pd.put("testTime", value);
                                break;
                            case 6:
                                pd.put("channel", value);
                                break;
                            case 7:
                                pd.put("statusCode", value);
                                break;
                            case 8:
                                pd.put("sequCode", value);
                                break;
                            case 9:
                                pd.put("frequency", value);
                                break;
                            case 10:
                                pd.put("type", value);
                                break;
                            case 11:
                                pd.put("forwardLv", value);
                                break;
                            case 12:
                                pd.put("reverseLv", value);
                                break;
                            case 13:
                                pd.put("forwardHv", value);
                                break;
                            case 14:
                                pd.put("reverseHv", value);
                                break;
                            case 15:
                                pd.put("capLeakage", value);
                                break;
                            case 16:
                                pd.put("bridge", value);
                                break;
                            case 17:
                                pd.put("cap", value);
                                break;
                            case 18:
                                pd.put("uploadTime", value);
                                break;
                            case 19:
                                pd.put("spare01", value);
                                break;
                            case 20:
                                pd.put("spare02", value);
                                break;
                            case 21:
                                pd.put("spare03", value);
                                break;
                            case 22:
                                pd.put("spare04", value);
                                break;
                            case 23:
                                pd.put("spare05", value);
                                break;
                            case 24:
                                pd.put("spare06", value);
                                break;
                            case 25:
                                pd.put("spare07", value);
                                break;
                            case 26:
                                pd.put("spare08", value);
                                break;
                            case 27:
                                pd.put("spare09", value);
                                break;
                            case 28:
                                pd.put("spare04", value);
                                break;

                            default:
                                break;
                        }

                    }
                    list.add(pd);
                }
            }
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
        }
        return list;
    }
    /**
     * 读取excel表
     *
     * @param inputStream
     * @return
     */
    public static List<PageData> excelToShopIdList(InputStream inputStream) {
        List<PageData> list = new ArrayList<>();
        Workbook workbook = null;
        try {
            workbook = WorkbookFactory.create(inputStream);
            inputStream.close();
            //工作表对象
            Sheet sheet = workbook.getSheetAt(0);
            //工作表的行
            Row row;
            //得到指定的单元格
            Cell cell;
            PageData pd;
            for (int rIndex = sheet.getFirstRowNum() + 1; rIndex <= sheet.getLastRowNum(); rIndex++) {   //遍历行
                System.out.println("rIndex: " + rIndex);
                row = sheet.getRow(rIndex);
                if (row != null) {
                    int firstCellIndex = row.getFirstCellNum();
                    int lastCellIndex = row.getLastCellNum();
                    pd = new PageData();
                    for (int cIndex = firstCellIndex; cIndex < lastCellIndex; cIndex++) {   //遍历列
                        cell = row.getCell(cIndex);
                        String value = "";
                        if (cell.getCellType().equals(CellType.NUMERIC)) {
                            value = NumberToTextConverter.toText(cell.getNumericCellValue()).trim();
                        } else if (cell.getCellType().equals(CellType.STRING)) {
                            value = cell.getStringCellValue().trim();
                        }
                        switch (cIndex) {
                            case 0:
                                pd.put("organization", value);
                                break;
                            case 1:
                                pd.put("department", value);
                                break;
                            case 2:
                                pd.put("userName", value);
                                pd.put("userCode", generateAbbreviation(value));
                                break;
                            case 3:
                                pd.put("mobilePhone", value);
                                break;
                            default:
                                break;
                        }

                    }
                    list.add(pd);
                }
            }
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
        }
        return list;
    }

    /**
     * 读取excel表
     *
     * @param inputStream
     * @return
     */
    public static List<PageData> excelToShopIdList(InputStream inputStream,int sheetNum) {
        List<PageData> list = new ArrayList<>();
        Workbook workbook = null;
        try {
            workbook = WorkbookFactory.create(inputStream);
            inputStream.close();
            //工作表对象
            Sheet sheet = workbook.getSheetAt(sheetNum);
            //工作表的行
            Row row;
            //得到指定的单元格
            Cell cell;
            PageData pd;
            for (int rIndex = sheet.getFirstRowNum() + 1; rIndex <= sheet.getLastRowNum(); rIndex++) {   //遍历行
                System.out.println("rIndex: " + rIndex);
                row = sheet.getRow(rIndex);
                if (row != null) {
                    int firstCellIndex = row.getFirstCellNum();
                    int lastCellIndex = row.getLastCellNum();
                    pd = new PageData();
                    for (int cIndex = firstCellIndex; cIndex < lastCellIndex; cIndex++) {   //遍历列
                        cell = row.getCell(cIndex);
                        String value = "";
                        if (cell.getCellType().equals(CellType.NUMERIC)) {
                            if(HSSFDateUtil.isCellDateFormatted(cell)){
                                value = DateUtil.getSdfDays2(cell.getDateCellValue());
                            }else {
                                value = NumberToTextConverter.toText(cell.getNumericCellValue()).trim();
                            }
                        } else if (cell.getCellType().equals(CellType.STRING)) {
                            value = cell.getStringCellValue().trim();
                        }
                        if (sheetNum == 0){
                            switch (cIndex) {
                                case 0:
                                    pd.put("userCode", value.trim());
                                    break;
                                case 1:
                                    pd.put("team", value.trim());
                                    break;
                                case 2:
                                    pd.put("lineBody", value.trim());
                                    break;
                                case 3:
                                    pd.put("wpCode", value.trim());
                                    break;
                                case 4:
                                    pd.put("station", value.trim());
                                    break;
                                case 5:
                                    pd.put("orderNum", value.trim());
                                    break;
                                case 6:
                                    pd.put("bot", value.trim());
                                    break;
                                case 7:
                                    pd.put("top", value.trim());
                                    break;
                                case 8:
                                    pd.put("useTime", value.trim());
                                    break;
                                default:
                                    break;
                            }
                        }else if (sheetNum == 1){
                            switch (cIndex) {
                                case 0:
                                    pd.put("userCode", value.trim());
                                    break;
                                case 1:
                                    pd.put("produceErrorCode", value.trim());
                                    break;
                                case 2:
                                    pd.put("unqualifiedQuantity", value.trim());
                                    break;
                                default:
                                    break;
                            }
                        }else if (sheetNum == 2){
                            switch (cIndex) {
                                case 0:
                                    pd.put("userCode", value.trim());
                                    break;
                                case 1:
                                    pd.put("materialCode", value.trim());
                                    break;
                                case 2:
                                    pd.put("materialName", value.trim());
                                    break;
                                case 3:
                                    pd.put("materialNum", value.trim());
                                    break;
                                default:
                                    break;
                            }
                        }

                    }
                    list.add(pd);
                }
            }
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
        }
        return list;
    }


    // 常见复姓列表
    private static final String[] COMPOUND_SURNAMES = {
            "欧阳", "太史", "端木", "上官", "司马", "东方", "独孤", "南宫", "万俟",
            "闻人", "夏侯", "诸葛", "尉迟", "公羊", "赫连", "澹台", "皇甫", "宗政",
            "濮阳", "公冶", "太叔", "申屠", "公孙", "慕容", "仲孙", "钟离", "长孙",
            "宇文", "司徒", "鲜于", "司空", "闾丘", "子车", "亓官", "司寇", "巫马",
            "公西", "颛孙", "壤驷", "公良", "漆雕", "乐正", "宰父", "谷梁", "拓跋",
            "夹谷", "轩辕", "令狐", "段干", "百里", "呼延", "东郭", "南门", "羊舌",
            "微生", "公户", "公玉", "公仪", "梁丘", "公仲", "公上", "公门", "公山",
            "公坚", "左丘", "公伯", "西门", "公祖", "第五", "公乘", "贯丘", "公皙",
            "南荣", "东里", "东宫", "仲长", "子书", "子桑", "即墨", "达奚", "褚师"
    };

    /**
     * 生成姓名的拼音首字母缩写（小写）
     * 例如："王宝瑞" → "wbr"，"上官婉儿" → "sgwe"
     */
    public static String generateAbbreviation(String fullName) {
        if (fullName == null || fullName.trim().isEmpty()) {
            return "";
        }

        fullName = fullName.trim();
        StringBuilder result = new StringBuilder();

        // 处理中文姓名
        if (containsChineseCharacter(fullName)) {
            // 提取姓氏和名字
            String surname = extractChineseSurname(fullName);
            String givenName = fullName.substring(surname.length());

            // 处理姓氏的首字母
            for (char c : surname.toCharArray()) {
                result.append(getChineseCharInitial(c));
            }

            // 处理名字的首字母
            for (char c : givenName.toCharArray()) {
                result.append(getChineseCharInitial(c));
            }
        }
        // 处理英文姓名（取每个单词的首字母）
        else {
            String[] nameParts = fullName.split("\\s+");
            for (String part : nameParts) {
                if (!part.isEmpty()) {
                    result.append(Character.toLowerCase(part.charAt(0)));
                }
            }
        }

        return result.toString();
    }

    /**
     * 判断字符串是否包含中文字符
     */
    private static boolean containsChineseCharacter(String str) {
        for (char c : str.toCharArray()) {
            if (isChineseCharacter(c)) {
                return true;
            }
        }
        return false;
    }

    /**
     * 判断字符是否为中文字符
     */
    private static boolean isChineseCharacter(char c) {
        Character.UnicodeBlock ub = Character.UnicodeBlock.of(c);
        return ub == Character.UnicodeBlock.CJK_UNIFIED_IDEOGRAPHS ||
                ub == Character.UnicodeBlock.CJK_COMPATIBILITY_IDEOGRAPHS ||
                ub == Character.UnicodeBlock.CJK_UNIFIED_IDEOGRAPHS_EXTENSION_A;
    }

    /**
     * 提取中文姓氏（考虑复姓）
     */
    private static String extractChineseSurname(String fullName) {
        // 检查常见复姓
        for (String compoundSurname : COMPOUND_SURNAMES) {
            if (fullName.startsWith(compoundSurname)) {
                return compoundSurname;
            }
        }

        // 默认取第一个字符为姓氏
        return fullName.substring(0, 1);
    }

    /**
     * 获取中文字符的拼音首字母（小写）
     */
    private static char getChineseCharInitial(char c) {
        if (!isChineseCharacter(c)) {
            return Character.toLowerCase(c);
        }

        HanyuPinyinOutputFormat format = new HanyuPinyinOutputFormat();
        format.setCaseType(HanyuPinyinCaseType.LOWERCASE);
        format.setToneType(HanyuPinyinToneType.WITHOUT_TONE);

        try {
            String[] pinyinArray = PinyinHelper.toHanyuPinyinStringArray(c, format);
            if (pinyinArray != null && pinyinArray.length > 0) {
                return pinyinArray[0].charAt(0);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

        // 如果无法获取拼音，返回原字符的小写形式
        return Character.toLowerCase(c);
    }

    // 示例用法

}
